package com.a51integrated.sfs2x;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.ArrayList;

import com.smartfoxserver.v2.db.IDBManager;
import com.smartfoxserver.v2.entities.Room;
import com.smartfoxserver.v2.entities.User;
import com.smartfoxserver.v2.entities.data.SFSObject;
import com.smartfoxserver.v2.entities.data.ISFSObject;
import com.smartfoxserver.v2.extensions.BaseClientRequestHandler;
import com.smartfoxserver.v2.extensions.ExtensionLogLevel;

public class GameListRequestHandler extends BaseClientRequestHandler {

	@Override
	public void handleClientRequest(User player, ISFSObject params) {
		// TODO Auto-generated method stub

		// Ask the database for a list of all the games that are currently running, and return it to the client in a correct format.
		Room last_room = player.getLastJoinedRoom();
		if (last_room != null)
		{
			String room_name = last_room.getName();
			
			// Get password from DB.
			IDBManager dbManager = getParentExtension().getParentZone().getDBManager();
			Connection connection;
			try
			{
				// Grab a connection from the DBManager connection pool.
				connection = dbManager.getConnection();
				
				// Firstly, count the number of rows in the first column of table games.
				PreparedStatement stmt = connection.prepareStatement("SELECT COUNT('names') FROM games");
				ResultSet res = stmt.executeQuery();
				
				// Verify that one record was found.
				if (!res.first())
				{
					trace(ExtensionLogLevel.WARN, "SQL Empty Resultset for room" + room_name);
					connection.close();
					return;
				}
				
				// Store the number of rows. (Index starts at 1)
				int NumRows = res.getInt(1);
				
				if (NumRows == 0)
				{
					connection.close();
					
					// Since no games were found, any games that are still in the user's session list can be removed.
					ISFSObject response = new SFSObject();
					response.putBool("empty", true);

					// Send the data back to the user.
					send("gameslist", response, player);
					
					return;
				}
				
				// Now obtain the data itself.
				stmt = connection.prepareStatement("SELECT * FROM games");
				res = stmt.executeQuery();
				
				if (!res.first())
				{
					trace(ExtensionLogLevel.WARN, "SQL Empty Resultset for room" + room_name);
					connection.close();
					
					return;
				}
				
				Collection<String> names = new ArrayList<String>();
				Collection<Integer> connectedplayers = new ArrayList<Integer>();
				//Collection<String> deprecated_names = new ArrayList<String>();	// Collection of game names that are now deprecated and should be removed from the database.
				
				// Get the information of the games. This loop is also used to construct a list of
				// games that are no longer in use (0 connected players).
				// These games must not be sent to the player and must be removed from the database (along with all tables associated with them).
				for (int i = 0; i < NumRows; ++i)
				{
					String name = res.getString("names");
					int connected = res.getInt("numconnected");
					
					// If there is atleast 1 player connected to the game, it's still valid.
					if (connected > 0)
					{					
						names.add(name);
						connectedplayers.add(connected);
					}
					// Else, the game should not be passed to the player and should be removed from the database.
					//else
					//{
					//	deprecated_names.add(name);
					//}
					
					// Advance the cursor.
					res.next();
				}
				
				// Send query in correct format back to the user.
				ISFSObject response = new SFSObject();
				response.putBool("empty", false);
				response.putUtfStringArray("GameNames", names);
				response.putIntArray("ConnectedPlayers", connectedplayers);

				// Send the data back to the user.
				send("gameslist", response, player);
				
				/*
				// Now that the user can receive the data, the server must clean up any deprecated games and tables.
				if (deprecated_names.size() > 0)
				{
					String removeGamesString = "DELETE FROM games WHERE ";
					for (Iterator it = deprecated_names.iterator(); it.hasNext(); )
					{
						String iterator_value = (String)it.next();
						removeGamesString += "names='" + iterator_value + "' OR ";
						
						// Send two statements to delete the tables that were created specifically for a game.
						stmt = connection.prepareStatement("DROP TABLE IF EXISTS `game_" + iterator_value + "_players`");
						stmt.execute();
						stmt = connection.prepareStatement("DROP TABLE IF EXISTS `game_" + iterator_value + "_gamedata`");
						stmt.execute();
					}
					// Add a useless part to the end of the statement because there's a trailing || operator.
					removeGamesString += "0=1";
					
					// Now prepare and execute the statement to delete rows from the games table.
					trace(removeGamesString);
					stmt = connection.prepareStatement(removeGamesString);
					stmt.execute();
				}
				*/				
				

				// Return connection to the DBMananger connection pool.
				connection.close();
			}
			catch(SQLException e)
			{
				trace(ExtensionLogLevel.WARN, "SQL Failed: " + e.toString());
			}
		}
		else
		{
			trace("No valid room found for user request.");
		}
	}

}
